﻿

// data structure 

db.doc2.insert([


    {
        cid: "1",
        mid: "0",
        theme: [
            {
                code: "E",
                weight: .25,

                topics: [

                    {
                        code: "E.1",
                        weight: .25,
                        indicators: [{ code: "E.1.1", weight: .1, score: 5 }, { code: "E.1.3", weight: .1, score: 5 }, { code: "E.1.4", weight: .1, score: 5 }]
                    },
                    {
                        code: "E.2",
                        weight: .25,
                        indicators: [{ code: "E.2.1", weight: .1, score: 5 }, { code: "E.2.3", weight: .1, score: 5 }, { code: "E.2.4", weight: .1, score: 5 }]
                    },
                    {
                        code: "E.3",
                        weight: .25,
                        indicators: [{ code: "E.3.2", weight: .1, score: 5 }, { code: "E.3.3", weight: .1, score: 5 }, { code: "E.3.4", weight: .1, score: 5 }]
                    },
                    {
                        code: "E.4",
                        weight: .25,
                        indicators: [{ code: "E.4.2", weight: .1, score: 5 }, { code: "E.4.3", weight: .1, score: 5 }, { code: "E.4.4", weight: .1, score: 5 }]
                    },
                ]
            }
            ,
            {
                code: "S",
                weight: .25,

                topics: [

                    {
                        code: "S.1",
                        weight: .25,
                        indicators: [{ code: "S.1.1", weight: .1, score: 5 }, { code: "S.1.3", weight: .1, score: 5 }, { code: "S.1.4", weight: .1, score: 5 }]
                    },
                    {
                        code: "S.2",
                        weight: .25,
                        indicators: [{ code: "S.2.1", weight: .1, score: 5 }, { code: "S.2.3", weight: .1, score: 5 }, { code: "S.2.4", weight: .1, score: 5 }]
                    },
                    {
                        code: "S.3",
                        weight: .25,
                        indicators: [{ code: "S.3.2", weight: .1, score: 5 }, { code: "S.3.3", weight: .1, score: 5 }, { code: "S.3.4", weight: .1, score: 5 }]
                    },
                    {
                        code: "S.4",
                        weight: .25,
                        indicators: [{ code: "S.4.2", weight: .1, score: 5 }, { code: "S.4.3", weight: .1, score: 5 }, { code: "S.4.4", weight: .1, score: 5 }]
                    },
                ]
            }
            ,
            {
                code: "G",
                weight: .25,

                topics: [

                    {
                        code: "G.1",
                        weight: .25,
                        indicators: [{ code: "G.1.2", weight: .1, score: 5 }, { code: "G.1.3", weight: .1, score: 5 }, { code: "G.1.4", weight: .1, score: 5 }]
                    },
                    {
                        code: "G.2",
                        weight: .25,
                        indicators: [{ code: "G.2.2", weight: .1, score: 5 }, { code: "G.2.3", weight: .1, score: 5 }, { code: "G.2.4", weight: .1, score: 5 }]
                    },
                    {
                        code: "G.3",
                        weight: .25,
                        indicators: [{ code: "G.3.2", weight: .1, score: 5 }, { code: "G.3.3", weight: .1, score: 5 }, { code: "G.3.4", weight: .1, score: 5 }]
                    },
                    {
                        code: "G.4",
                        weight: .25,
                        indicators: [{ code: "G.4.2", weight: .1, score: 5 }, { code: "G.4.3", weight: .1, score: 5 }, { code: "G.4.4", weight: .1, score: 5 }]
                    },
                ]
            }
        ]
    }
]);


// compute score method


   db.doc2.aggregate([
    { $unwind:  "$theme" }, 
    { $unwind:  "$theme.topics" },
    { $unwind:  "$theme.topics.indicators" },
    { $project: {    
        _id: "$_id",
        cid: "$cid",
        mid: "$mid",
        themeCode : "$theme.code",
        themeWeight: "$theme.weight",
        topicCode:  "$theme.topics.code",
        topicWeight: "$theme.topics.weight",
        indicatorCode: "$theme.topics.indicators.code",
        indicatorWeight: "$theme.topics.indicators.weight",
        indicatorScore: "$theme.topics.indicators.score"
    }
    }
    ,
    { $group:  { 
        _id: {
            _id:  "$_id",
            cid: "$cid",
            mid: "$mid",
            themeCode : "$themeCode",
            themeWeight: "$themeWeight",
            topicCode:  "$topicCode",
            topicWeight: "$topicWeight"
        }, 
        indicators: { $push : { code: "$indicatorCode", weight: "$indicatorWeight", score : "$indicatorScore"  } },  
        topicScore: { $sum : { $multiply : [ "$indicatorWeight"  , "$indicatorScore"]} } 
    }
    }
    ,

    { $group:  { 
        _id: {
            _id:  "$_id._id",                      
            cid: "$_id.cid",
            mid: "$_id.mid",
            themeCode : "$_id.themeCode",                             
            themeWeight:"$_id.themeWeight"
        },                       
        topics:  { $push: { code: "$_id.topicCode" ,weight: "$_id.topicWeight" ,score: "$topicScore" ,indicators: "$indicators" } },
        themeScore: { $sum : { $multiply : [ "$_id.topicWeight"  , "$topicScore"]} } 
    }
    }
    ,
    { $group:  { 
        _id: {
            _id:  "$_id._id",                      
            cid: "$_id.cid",
            mid: "$_id.mid",
        },       
        theme: { $push: { code: "$_id.themeCode" ,weight: "$_id.themeWeight" ,score: "$themeScore" ,topics: "$topics" } },
        totalScore: { $sum : { $multiply : [ "$_id.themeWeight"  , "$themeScore"]} } 
    }
    }
    ,
    {
        $project: { 
            _id:  "$_id._id",                      
            cid: "$_id.cid",
            mid: "$_id.mid",                
            companyScore:"$totalScore",
            theme:"$theme",
        } 
    }
    ,
    {
        $out: "doc2results" //doc2
    }

   ]);


   db.doc2.aggregate([
   { $unwind: "$theme" },
   { $unwind: "$theme.topics" },
   //{ $unwind:  "$theme.topics.indicators" },
   {
       $group:
         {
             _id:
             {
                 _id: "$_id",
                 cid: "$cid",
                 mid: "$mid",
                 companyScore: "$companyScore"
             }
               , E: { $sum: { $cond: [{ $eq: ["$theme.code", "E"] }, "$theme.score", ""] } }
               , S: { $sum: { $cond: [{ $eq: ["$theme.code", "S"] }, "$theme.score", ""] } }
               , G: { $sum: { $cond: [{ $eq: ["$theme.code", "G"] }, "$theme.score", ""] } }
               , P: { $sum: { $cond: [{ $eq: ["$theme.code", "P"] }, "$theme.score", ""] } }
               , E_3: { $sum: { $cond: [{ $and: [{ $eq: ["$theme.code", "E"] }, { $eq: ["$theme.topics.code", "E.3"] }] }, "$theme.topics.score", ""] } }
               , S_4: { $sum: { $cond: [{ $and: [{ $eq: ["$theme.code", "S"] }, { $eq: ["$theme.topics.code", "S.4"] }] }, "$theme.topics.score", ""] } }

               , E_3_2: { $sum: { $cond: [{ $and: [{ $eq: ["$theme.code", "E"] }, { $eq: ["$theme.topics.code", "E.3"] }, { $eq: ["$theme.topics.indicators.code", "E.3.2"] }] }, "$theme.topics.indicators.score", ""] } }
               , S_4_1: { $sum: { $cond: [{ $and: [{ $eq: ["$theme.code", "S"] }, { $eq: ["$theme.topics.code", "S.4"] }, { $eq: ["$theme.topics.indicators.code", "S.4.1"] }] }, "$theme.topics.indicators.score", ""] } }
         }
   }

   ]);









